package com.tim.dao.Impl;

import com.tim.dao.CellDao;
import com.tim.pojo.Cell;
import com.tim.util.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

/**
 * 作者：Tim
 * 时间：2021/4/28 10:52
 */
public class CellDaoImpl implements CellDao {
    @Override
    public int deleteCell(int id) throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
        String sql = "delete from Cell where id = ?";
        Object[] args ={id};
        return qr.update(sql,args);
    }

    @Override
    public int addCell(Cell cell) throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
        String sql = "insert into cell (id,plotName,cellNum,cellFamily,cellDetail,cellTime) values (?,?,?,?,?,?)";
        Object[] args = {cell.getId(),cell.getPlotName(),cell.getCellNum(),cell.getCellFamily(),cell.getCellDetail(),cell.getCellTime()};
        return qr.update(sql,args);
    }

    @Override
    public Cell selectOne(int id) throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
        String sql = "select * from cell where id = ?";
        Object[] args = {id};
        return qr.query(sql,new BeanHandler<Cell>(Cell.class),args);
    }

    @Override
    public int updateCell(Cell cell) throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
        String sql ="update cell set plotName=?,cellNum=?,cellFamily=?,cellDetail=?,cellTime=? where id=?";
        Object[] args ={cell.getPlotName(),cell.getCellNum(),cell.getCellFamily(),cell.getCellDetail(),cell.getCellTime(),cell.getId()};
        return  qr.update(sql,args);
    }

    @Override
    public List<Cell> searchCell(String plotName) throws SQLException {
        return null;
    }

    @Override
    public List<Cell> limit(Integer currentPage, Integer pageSize, String plotName) throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
        if(plotName==null){
            String sql ="select * from cell limit ?,?";
            Object[] args = {(currentPage-1)*pageSize,pageSize};
            return qr.query(sql,new BeanListHandler<Cell>(Cell.class),args);
        }else{
            String sql ="select * from cell where plotName = ? limit ?,?";
            Object[] args = {"%"+plotName+"%",(currentPage-1)*pageSize,pageSize};
            return qr.query(sql,new BeanListHandler<Cell>(Cell.class),args);
        }
    }

    @Override
    public int count(String plotName) throws SQLException {
        QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
        if(plotName == null){
            String sql = "select count(*) from cell";
            return qr.query(sql,new ScalarHandler<Number>()).intValue();
        }else{
            String sql = "select count(*) from cell where plotName like ?";
            return qr.query(sql,new ScalarHandler<Number>(),"%"+plotName+"%").intValue();
        }
    }
}
